package laps.dataaccess.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import laps.dataaccess.DAOFactory;
import laps.dataaccess.LeaveRecordDAO;
import laps.model.Employee;
import laps.model.LeaveRecord;

public class LeaveRecordDAOImpl implements LeaveRecordDAO {

	private Connection connection;

	public LeaveRecordDAOImpl(Connection connection) {
		super();
		this.connection = connection;
	}

	@Override
	public LeaveRecord getLeaveById(int leave_record_id) {

		String selectSql = "Select * from leave_record where leave_record_id="
				+ leave_record_id;
		LeaveRecord leaveRecord = new LeaveRecord();

		try {
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(selectSql);
			while (resultSet.next()) {
				leaveRecord.setLeave_type(resultSet.getString("leave_type"));
				leaveRecord.setApplied_by(resultSet.getInt("applied_by"));
				leaveRecord.setApproved_by(resultSet.getInt("approved_by"));
				leaveRecord.setStatus(resultSet.getString("status"));
				leaveRecord.setStaff_comment(resultSet
						.getString("staff_comment"));
				leaveRecord.setManager_comment(resultSet
						.getString("manager_comment"));
				leaveRecord.setContact_details(resultSet
						.getString("contact_details"));
				leaveRecord.setWork_dissemination(resultSet
						.getString("work_dissemination"));
				leaveRecord.setNum_of_leave_days(resultSet
						.getInt("num_of_leave_days"));
				leaveRecord.setStart_date(resultSet.getDate("start_date"));
				leaveRecord.setEnd_date(resultSet.getDate("end_date"));
				leaveRecord.setApplication_date(resultSet
						.getDate("application_date"));
				leaveRecord.setYear(resultSet.getInt("year"));

			}
			statement.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DAOFactory.getInstance().closeConnection();
		}

		return leaveRecord;
	}

	@Override
	public ArrayList<LeaveRecord> getLeaveApprovedBy(Employee employee) {

		ArrayList<LeaveRecord> list = new ArrayList<LeaveRecord>();
		String SelectSql = "Select * from leave_record where approved_by"
				+ employee.getEmployee_id();

		Statement statement;
		try {
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(SelectSql);
			LeaveRecord leaveRecord = new LeaveRecord();

			while (resultSet.next()) {
				leaveRecord.setLeave_record_id(resultSet
						.getInt("leave_record_id"));
				leaveRecord.setLeave_type(resultSet.getString("leave_type"));
				leaveRecord.setApplied_by(resultSet.getInt("applied_by"));
				leaveRecord.setApproved_by(resultSet.getInt("approved_by"));
				leaveRecord.setStatus(resultSet.getString("status"));
				leaveRecord.setStaff_comment(resultSet
						.getString("staff_comment"));
				leaveRecord.setManager_comment(resultSet
						.getString("manager_comment"));
				leaveRecord.setContact_details(resultSet
						.getString("contact_details"));
				leaveRecord.setWork_dissemination(resultSet
						.getString("work_dissemination"));
				leaveRecord.setNum_of_leave_days(resultSet
						.getInt("num_of_leave_days"));
				leaveRecord.setStart_date(resultSet.getDate("start_date"));
				leaveRecord.setEnd_date(resultSet.getDate("end_date"));
				leaveRecord.setApplication_date(resultSet
						.getDate("application_date"));
				leaveRecord.setYear(resultSet.getInt("year"));
				list.add(leaveRecord);

			}
			statement.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DAOFactory.getInstance().closeConnection();
		}

		return list;
	}

	@Override
	public void addNewLeave(LeaveRecord leaveRecord) {

		String insertSql = "insert into leave_record(leave_type,applied_by,approved_by,status,staff_comment,manager_comment,contact_details,work_dissemination,num_of_days,start_date,end_date,application_date,year) value ("
				+ leaveRecord.getApplied_by()
				+ ","
				+ leaveRecord.getApproved_by()
				+ ",'"
				+ leaveRecord.getStatus()
				+ "','"
				+ leaveRecord.getStaff_comment()
				+ "','"
				+ leaveRecord.getManager_comment()
				+ "','"
				+ leaveRecord.getContact_details()
				+ "','"
				+ leaveRecord.getWork_dissemination()
				+ "',"
				+ leaveRecord.getNum_of_leave_days()
				+ ",'"
				+ leaveRecord.getStart_date()
				+ "','"
				+ leaveRecord.getEnd_date()
				+ "','"
				+ leaveRecord.getApplication_date()
				+ "',"
				+ leaveRecord.getYear() + ");";

		Statement statement;
		try {
			statement = connection.createStatement();
			int result = statement.executeUpdate(insertSql);

			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DAOFactory.getInstance().closeConnection();
		}

	}

	@Override
	public void updateLeave(LeaveRecord leaveRecord) {

		String UpdateSql = "update leave_record set leave_type='"
				+ leaveRecord.getLeave_type() + "',applied_by='"
				+ leaveRecord.getApplied_by() + "',approved_by="
				+ leaveRecord.getApproved_by() + ",status='"
				+ leaveRecord.getStatus() + "',staff_comment='"
				+ leaveRecord.getStaff_comment() + "',manager_comment='"
				+ leaveRecord.getManager_comment() + "',contact_details='"
				+ leaveRecord.getContact_details() + "',work_dissemination='"
				+ leaveRecord.getWork_dissemination() + "',num_of_days="
				+ leaveRecord.getNum_of_leave_days() + ",start_date='"
				+ leaveRecord.getStart_date() + "',end_date='"
				+ leaveRecord.getEnd_date() + "',application_date='"
				+ leaveRecord.getEnd_date() + "',year=" + leaveRecord.getYear()
				+ " where leave_record_id=" + leaveRecord.getLeave_record_id()
				+ ";";

		Statement statement;
		try {
			statement = connection.createStatement();
			int result = statement.executeUpdate(UpdateSql);

			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void deleteLeave(LeaveRecord leaveRecord) {
		String DeleteSql = "Delete From leave_record where leave_record_id"
				+ leaveRecord.getLeave_record_id();

		Statement statement;
		try {
			statement = connection.createStatement();
			int rows = statement.executeUpdate(DeleteSql);

			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		finally {
			DAOFactory.getInstance().closeConnection();
		}

	}

}
